package airarmory.orm.dao
{
	/**
	* @author www.comtaste.com
	*/
	
	import flash.data.SQLConnection;
	import flash.events.SQLEvent;
	import flash.events.SQLErrorEvent;
	import flash.data.SQLStatement;
	import flash.errors.SQLError;
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	
	import airarmory.orm.vo.TblItemVO;
	
	public class TblItem_DAO
	{
	
		
		private static var instance:TblItem_DAO;
		public static function getInstance():TblItem_DAO {
			if( instance == null )
				instance = new TblItem_DAO( new SingletonLock );
			return instance;
		}
		
	
		public function TblItem_DAO( lock: SingletonLock) {
		}
	
		private var sqlConnection:SQLConnection;
		public function getConnection():SQLConnection {
			return sqlConnection;
		}
		public function setConnection( connection:SQLConnection, initializeTable:Boolean = false ):void {
			// store connection reference
			sqlConnection = connection;
			// try construct table on Database any time a new connection is submitted
			if(sqlConnection.connected && initializeTable){
				createTable();
				
			}
		}
	
		public function createTable( resultHandler:Function = null, faultHandler:Function = null ):void {
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = sqlConnection;
			stmt.text = 'CREATE TABLE IF NOT EXISTS TblItem (id INTEGER PRIMARY KEY AUTOINCREMENT,         item_id TEXT,        item_name TEXT,       is_token BOOLEAN NOT NULL DEFAULT false,       slot TEXT,     type TEXT,    is_unique BOOLEAN NOT NULL DEFAULT false,    fit_tank BOOLEAN NOT NULL DEFAULT false,    fit_caster BOOLEAN NOT NULL DEFAULT false,    fit_melee BOOLEAN NOT NULL DEFAULT false,    fit_warrior BOOLEAN NOT NULL DEFAULT false,    fit_paladin BOOLEAN NOT NULL DEFAULT false,    fit_hunter BOOLEAN NOT NULL DEFAULT false,    fit_rogue BOOLEAN NOT NULL DEFAULT false,    fit_priest BOOLEAN NOT NULL DEFAULT false,    fit_deathknight BOOLEAN NOT NULL DEFAULT false,    fit_shaman BOOLEAN NOT NULL DEFAULT false,    fit_mage BOOLEAN NOT NULL DEFAULT false,    fit_warlock BOOLEAN NOT NULL DEFAULT false,    fit_druid BOOLEAN NOT NULL DEFAULT false, item_level INTEGER);';
			stmt.addEventListener( SQLEvent.RESULT,
			function ( event:SQLEvent ):void {
				if (resultHandler != null) resultHandler.call(this);
			});
			stmt.addEventListener( SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler );
			stmt.execute();
		}
		
		public function getTableContent( resultHandler:Function, faultHandler:Function = null ):void {
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = sqlConnection;
			stmt.text = 'SELECT * FROM TblItem;';
			stmt.itemClass = TblItemVO;
			stmt.addEventListener( SQLEvent.RESULT,
			function ( event:SQLEvent ):void {
				resultHandler.call( this, new ArrayCollection( stmt.getResult().data ) );
			});
			stmt.addEventListener( SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler );
			stmt.execute();
		}
		
		public function updateRow( rowItem:TblItemVO, resultHandler:Function = null, faultHandler:Function = null ):void {
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = sqlConnection;
			stmt.text = 'UPDATE TblItem SET id = @id, item_id = @item_id, item_name = @item_name, is_token = @is_token, slot = @slot, type = @type, is_unique = @is_unique, fit_tank = @fit_tank, fit_caster = @fit_caster, fit_melee = @fit_melee, fit_warrior = @fit_warrior, fit_paladin = @fit_paladin, fit_hunter = @fit_hunter, fit_rogue = @fit_rogue, fit_priest = @fit_priest, fit_deathknight = @fit_deathknight, fit_shaman = @fit_shaman, fit_mage = @fit_mage, fit_warlock = @fit_warlock, fit_druid = @fit_druid, item_level = @item_level WHERE ID = @id;';
			setParameters( stmt, [ {name:"id", value:rowItem.id}, {name:"item_id", value:rowItem.item_id}, {name:"item_name", value:rowItem.item_name}, {name:"is_token", value:rowItem.is_token}, {name:"slot", value:rowItem.slot}, {name:"type", value:rowItem.type}, {name:"is_unique", value:rowItem.is_unique}, {name:"fit_tank", value:rowItem.fit_tank}, {name:"fit_caster", value:rowItem.fit_caster}, {name:"fit_melee", value:rowItem.fit_melee}, {name:"fit_warrior", value:rowItem.fit_warrior}, {name:"fit_paladin", value:rowItem.fit_paladin}, {name:"fit_hunter", value:rowItem.fit_hunter}, {name:"fit_rogue", value:rowItem.fit_rogue}, {name:"fit_priest", value:rowItem.fit_priest}, {name:"fit_deathknight", value:rowItem.fit_deathknight}, {name:"fit_shaman", value:rowItem.fit_shaman}, {name:"fit_mage", value:rowItem.fit_mage}, {name:"fit_warlock", value:rowItem.fit_warlock}, {name:"fit_druid", value:rowItem.fit_druid}, {name:"item_level", value:rowItem.item_level} ] );
			stmt.addEventListener( SQLEvent.RESULT,
			function ( event:SQLEvent ):void {
				if ( resultHandler != null ) resultHandler.call( this, rowItem );
			});
			stmt.addEventListener( SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler );
			stmt.execute();
		}
		
		public function insertRow( rowItem:TblItemVO, resultHandler:Function = null, faultHandler:Function = null ):void {
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = sqlConnection;
			stmt.text = 'INSERT INTO TblItem( item_id, item_name, is_token, slot, type, is_unique, fit_tank, fit_caster, fit_melee, fit_warrior, fit_paladin, fit_hunter, fit_rogue, fit_priest, fit_deathknight, fit_shaman, fit_mage, fit_warlock, fit_druid, item_level ) VALUES ( @item_id,@item_name,@is_token,@slot,@type,@is_unique,@fit_tank,@fit_caster,@fit_melee,@fit_warrior,@fit_paladin,@fit_hunter,@fit_rogue,@fit_priest,@fit_deathknight,@fit_shaman,@fit_mage,@fit_warlock,@fit_druid,@item_level );';
			var params:Array = [ {name:"item_id", value:rowItem.item_id}, {name:"item_name", value:rowItem.item_name}, {name:"is_token", value:rowItem.is_token}, {name:"slot", value:rowItem.slot}, {name:"type", value:rowItem.type}, {name:"is_unique", value:rowItem.is_unique}, {name:"fit_tank", value:rowItem.fit_tank}, {name:"fit_caster", value:rowItem.fit_caster}, {name:"fit_melee", value:rowItem.fit_melee}, {name:"fit_warrior", value:rowItem.fit_warrior}, {name:"fit_paladin", value:rowItem.fit_paladin}, {name:"fit_hunter", value:rowItem.fit_hunter}, {name:"fit_rogue", value:rowItem.fit_rogue}, {name:"fit_priest", value:rowItem.fit_priest}, {name:"fit_deathknight", value:rowItem.fit_deathknight}, {name:"fit_shaman", value:rowItem.fit_shaman}, {name:"fit_mage", value:rowItem.fit_mage}, {name:"fit_warlock", value:rowItem.fit_warlock}, {name:"fit_druid", value:rowItem.fit_druid}, {name:"item_level", value:rowItem.item_level} ];
			setParameters( stmt, params );
			stmt.addEventListener( SQLEvent.RESULT,
			function ( event:SQLEvent ):void {
				if (!rowItem.id > 0) rowItem.id = stmt.getResult().lastInsertRowID;
				if (resultHandler != null) resultHandler.call(this, rowItem);
			});
			stmt.addEventListener( SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler );
			stmt.execute();
		}
		
		public function deleteRow( rowItem:TblItemVO, resultHandler:Function = null, faultHandler:Function = null ):void {
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = sqlConnection;
			stmt.text = 'DELETE FROM TblItem WHERE TblItem.id = @id;';
			var params:Array = [ {name:"id", value:rowItem.id} ];
			setParameters( stmt, params );
			stmt.addEventListener( SQLEvent.RESULT,
			function ( event:SQLEvent ):void {
				if (resultHandler != null) resultHandler.call(this, rowItem);
			});
			stmt.addEventListener( SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler );
			stmt.execute();
		}
		
		private function setParameters( stmt:SQLStatement, params:Array ):void {
			var param:Object;
			for ( var i:int = 0; i < params.length; i++ ) {
				param = params[i];
				stmt.parameters[ '@' + param.name ] = param.value;
			}
		}
		
		private function sqlErrorHandler( event:SQLError ):void {
			Alert.show( event.message, "Error" );
		}
		
	}
}

class SingletonLock {}
